# -*- coding: utf-8 -*-
"""
表格转换器
支持 Excel、CSV 等格式的互转
"""

import os
import pandas as pd
import openpyxl
from openpyxl import load_workbook
import xlrd
import xlwt
from xlutils.copy import copy as xl_copy
import win32com.client
import pythoncom
import json


class SpreadsheetConverter:
    """表格转换器类"""

    def __init__(self):
        pass

    def convert(self, input_path, output_dir, conversion_type):
        """
        转换表格
        :param input_path: 输入文件路径
        :param output_dir: 输出目录
        :param conversion_type: 转换类型
        :return: 输出文件路径
        """
        if conversion_type == 'xls_to_xlsx':
            return self.xls_to_xlsx(input_path, output_dir)
        elif conversion_type == 'xlsx_to_xls':
            return self.xlsx_to_xls(input_path, output_dir)
        elif conversion_type == 'excel_to_csv':
            return self.excel_to_csv(input_path, output_dir)
        elif conversion_type == 'csv_to_excel':
            return self.csv_to_excel(input_path, output_dir)
        elif conversion_type == 'excel_to_pdf':
            return self.excel_to_pdf(input_path, output_dir)
        elif conversion_type == 'excel_to_json':
            return self.excel_to_json(input_path, output_dir)
        elif conversion_type == 'json_to_excel':
            return self.json_to_excel(input_path, output_dir)
        elif conversion_type == 'csv_to_json':
            return self.csv_to_json(input_path, output_dir)
        elif conversion_type == 'json_to_csv':
            return self.json_to_csv(input_path, output_dir)
        elif conversion_type == 'ods_to_excel':
            return self.ods_to_excel(input_path, output_dir)
        elif conversion_type == 'excel_to_ods':
            return self.excel_to_ods(input_path, output_dir)
        else:
            raise ValueError(f"不支持的转换类型: {conversion_type}")

    def xls_to_xlsx(self, xls_path, output_dir):
        """
        XLS转XLSX
        :param xls_path: XLS文件路径
        :param output_dir: 输出目录
        :return: 输出文件路径
        """
        if not os.path.exists(xls_path):
            raise FileNotFoundError(f"文件不存在: {xls_path}")

        if not xls_path.lower().endswith('.xls'):
            raise ValueError(f"不是XLS文件: {xls_path}")

        base_name = os.path.splitext(os.path.basename(xls_path))[0]
        output_path = os.path.join(output_dir, f"{base_name}.xlsx")

        # 使用pandas读取并保存
        try:
            # 读取所有工作表
            xls = pd.ExcelFile(xls_path, engine='xlrd')
            with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
                for sheet_name in xls.sheet_names:
                    df = pd.read_excel(xls, sheet_name=sheet_name)
                    df.to_excel(writer, sheet_name=sheet_name, index=False)
        except Exception as e:
            # 如果pandas失败，尝试使用win32com
            pythoncom.CoInitialize()
            try:
                excel = win32com.client.Dispatch("Excel.Application")
                excel.Visible = False
                wb = excel.Workbooks.Open(os.path.abspath(xls_path))
                wb.SaveAs(os.path.abspath(output_path), FileFormat=51)  # 51 = xlsx
                wb.Close()
                excel.Quit()
            except Exception as com_error:
                raise Exception(f"XLS转XLSX失败: {str(com_error)}")
            finally:
                pythoncom.CoUninitialize()

        if not os.path.exists(output_path):
            raise Exception("转换失败，输出文件未生成")

        return output_path

    def xlsx_to_xls(self, xlsx_path, output_dir):
        """
        XLSX转XLS
        :param xlsx_path: XLSX文件路径
        :param output_dir: 输出目录
        :return: 输出文件路径
        """
        base_name = os.path.splitext(os.path.basename(xlsx_path))[0]
        output_path = os.path.join(output_dir, f"{base_name}.xls")

        pythoncom.CoInitialize()
        try:
            excel = win32com.client.Dispatch("Excel.Application")
            excel.Visible = False
            wb = excel.Workbooks.Open(os.path.abspath(xlsx_path))
            wb.SaveAs(os.path.abspath(output_path), FileFormat=56)  # 56 = xls
            wb.Close()
            excel.Quit()
        finally:
            pythoncom.CoUninitialize()

        return output_path

    def excel_to_csv(self, excel_path, output_dir):
        """
        Excel转CSV
        :param excel_path: Excel文件路径
        :param output_dir: 输出目录
        :return: 输出文件路径列表
        """
        base_name = os.path.splitext(os.path.basename(excel_path))[0]

        # 读取所有工作表
        xls = pd.ExcelFile(excel_path)
        output_paths = []

        for sheet_name in xls.sheet_names:
            df = pd.read_excel(xls, sheet_name=sheet_name)

            # 生成输出文件名
            if len(xls.sheet_names) > 1:
                output_path = os.path.join(output_dir, f"{base_name}_{sheet_name}.csv")
            else:
                output_path = os.path.join(output_dir, f"{base_name}.csv")

            # 保存为CSV
            df.to_csv(output_path, index=False, encoding='utf-8-sig')
            output_paths.append(output_path)

        return output_paths[0] if len(output_paths) == 1 else output_paths

    def csv_to_excel(self, csv_path, output_dir):
        """
        CSV转Excel
        :param csv_path: CSV文件路径
        :param output_dir: 输出目录
        :return: 输出文件路径
        """
        if not os.path.exists(csv_path):
            raise FileNotFoundError(f"文件不存在: {csv_path}")

        if not csv_path.lower().endswith('.csv'):
            raise ValueError(f"不是CSV文件: {csv_path}")

        base_name = os.path.splitext(os.path.basename(csv_path))[0]
        output_path = os.path.join(output_dir, f"{base_name}.xlsx")

        try:
            # 尝试多种编码读取CSV
            encodings = ['utf-8-sig', 'utf-8', 'gbk', 'gb2312', 'latin1']
            df = None
            for encoding in encodings:
                try:
                    df = pd.read_csv(csv_path, encoding=encoding)
                    break
                except:
                    continue

            if df is None:
                raise Exception("无法读取CSV文件，请检查文件编码")

            # 保存为Excel
            df.to_excel(output_path, index=False, engine='openpyxl')
        except Exception as e:
            raise Exception(f"CSV转Excel失败: {str(e)}")

        if not os.path.exists(output_path):
            raise Exception("转换失败，输出文件未生成")

        return output_path

    def excel_to_pdf(self, excel_path, output_dir):
        """
        Excel转PDF - 高质量转换，保持表格格式和布局
        :param excel_path: Excel文件路径
        :param output_dir: 输出目录
        :return: 输出文件路径
        """
        if not os.path.exists(excel_path):
            raise FileNotFoundError(f"文件不存在: {excel_path}")

        ext = excel_path.lower()
        if not (ext.endswith('.xls') or ext.endswith('.xlsx')):
            raise ValueError(f"不是Excel文件: {excel_path}")

        base_name = os.path.splitext(os.path.basename(excel_path))[0]
        output_path = os.path.join(output_dir, f"{base_name}.pdf")

        print(f"\n开始转换: {os.path.basename(excel_path)}")
        print("=" * 60)

        # 方法1: 使用 win32com（最佳质量，需要Microsoft Office）
        try:
            print("方法1: 使用 Microsoft Excel COM - 最佳质量...")
            pythoncom.CoInitialize()
            try:
                excel = win32com.client.Dispatch("Excel.Application")
                excel.Visible = False
                excel.DisplayAlerts = False

                wb = excel.Workbooks.Open(os.path.abspath(excel_path))

                # 设置打印质量
                for ws in wb.Worksheets:
                    # 设置页面布局
                    ws.PageSetup.Zoom = False
                    ws.PageSetup.FitToPagesWide = 1
                    ws.PageSetup.FitToPagesTall = False
                    ws.PageSetup.Orientation = 1  # 纵向
                    ws.PageSetup.PaperSize = 9  # A4

                    # 设置边距
                    ws.PageSetup.LeftMargin = excel.CentimetersToPoints(1.5)
                    ws.PageSetup.RightMargin = excel.CentimetersToPoints(1.5)
                    ws.PageSetup.TopMargin = excel.CentimetersToPoints(1.5)
                    ws.PageSetup.BottomMargin = excel.CentimetersToPoints(1.5)

                    # 打印设置
                    ws.PageSetup.PrintGridlines = False
                    ws.PageSetup.PrintHeadings = False
                    ws.PageSetup.CenterHorizontally = False
                    ws.PageSetup.CenterVertically = False

                # 导出为高质量PDF
                # xlTypePDF = 0
                # xlQualityStandard = 0 (高质量)
                wb.ExportAsFixedFormat(
                    Type=0,  # PDF格式
                    Filename=os.path.abspath(output_path),
                    Quality=0,  # 0=标准质量, 1=最小文件大小
                    IncludeDocProperties=True,
                    IgnorePrintAreas=False,
                    OpenAfterPublish=False
                )

                wb.Close(SaveChanges=False)
                excel.Quit()

                if os.path.exists(output_path) and os.path.getsize(output_path) > 1000:
                    print("✓ Excel COM转换成功 - 完美保留表格格式")
                    return output_path
            except Exception as com_error:
                print(f"✗ Excel COM转换失败: {str(com_error)}")
            finally:
                pythoncom.CoUninitialize()
        except Exception as e:
            print(f"✗ 初始化COM失败: {str(e)}")

        # 方法2: 使用 LibreOffice
        try:
            print("\n方法2: 尝试使用 LibreOffice...")
            import subprocess

            libreoffice_paths = [
                r'C:\Program Files\LibreOffice\program\soffice.exe',
                r'C:\Program Files (x86)\LibreOffice\program\soffice.exe',
                'soffice'
            ]

            for lo_path in libreoffice_paths:
                try:
                    cmd = [
                        lo_path,
                        '--headless',
                        '--convert-to', 'pdf',
                        '--outdir', output_dir,
                        os.path.abspath(excel_path)
                    ]

                    result = subprocess.run(cmd, capture_output=True, timeout=60)

                    if os.path.exists(output_path) and os.path.getsize(output_path) > 1000:
                        print("✓ LibreOffice转换成功")
                        return output_path
                except Exception:
                    continue
        except Exception as e:
            print(f"✗ LibreOffice转换失败: {str(e)}")

        # 所有方法都失败
        print("\n" + "=" * 60)
        print("✗ 转换失败: 所有方法都无法处理此Excel文件")
        print("\n建议:")
        print("1. 安装 Microsoft Office (推荐，质量最佳)")
        print("2. 或安装 LibreOffice (免费替代)")
        print("3. 检查Excel文件是否已损坏")
        print("=" * 60)

        raise Exception("Excel转PDF失败：需要安装Microsoft Office或LibreOffice")

        return output_path

    def excel_to_json(self, excel_path, output_dir):
        """
        Excel转JSON
        :param excel_path: Excel文件路径
        :param output_dir: 输出目录
        :return: 输出文件路径
        """
        if not os.path.exists(excel_path):
            raise FileNotFoundError(f"文件不存在: {excel_path}")

        base_name = os.path.splitext(os.path.basename(excel_path))[0]

        print(f"\n开始转换: {os.path.basename(excel_path)}")
        print("=" * 60)

        try:
            # 读取所有工作表
            xls = pd.ExcelFile(excel_path)
            output_paths = []

            for sheet_name in xls.sheet_names:
                df = pd.read_excel(xls, sheet_name=sheet_name)

                # 生成输出文件名
                if len(xls.sheet_names) > 1:
                    output_path = os.path.join(output_dir, f"{base_name}_{sheet_name}.json")
                else:
                    output_path = os.path.join(output_dir, f"{base_name}.json")

                # 转换为JSON（records格式：数组包含对象）
                json_data = df.to_dict(orient='records')

                # 保存为JSON文件（格式化输出）
                with open(output_path, 'w', encoding='utf-8') as f:
                    json.dump(json_data, f, ensure_ascii=False, indent=2)

                output_paths.append(output_path)
                print(f"✓ 工作表 '{sheet_name}' 已转换")

            print("✓ Excel转JSON成功")
            return output_paths[0] if len(output_paths) == 1 else output_paths

        except Exception as e:
            print(f"✗ 转换失败: {str(e)}")
            raise Exception(f"Excel转JSON失败: {str(e)}")

    def json_to_excel(self, json_path, output_dir):
        """
        JSON转Excel
        :param json_path: JSON文件路径
        :param output_dir: 输出目录
        :return: 输出文件路径
        """
        if not os.path.exists(json_path):
            raise FileNotFoundError(f"文件不存在: {json_path}")

        if not json_path.lower().endswith('.json'):
            raise ValueError(f"不是JSON文件: {json_path}")

        base_name = os.path.splitext(os.path.basename(json_path))[0]
        output_path = os.path.join(output_dir, f"{base_name}.xlsx")

        print(f"\n开始转换: {os.path.basename(json_path)}")
        print("=" * 60)

        try:
            # 读取JSON文件
            with open(json_path, 'r', encoding='utf-8') as f:
                json_data = json.load(f)

            # 判断JSON结构
            if isinstance(json_data, list):
                # 数组格式：直接转换
                df = pd.DataFrame(json_data)
            elif isinstance(json_data, dict):
                # 对象格式：可能是多个工作表或单个数据
                if all(isinstance(v, list) for v in json_data.values()):
                    # 多工作表格式：{"Sheet1": [...], "Sheet2": [...]}
                    with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
                        for sheet_name, data in json_data.items():
                            df = pd.DataFrame(data)
                            df.to_excel(writer, sheet_name=sheet_name, index=False)
                    print("✓ JSON转Excel成功（多工作表）")
                    return output_path
                else:
                    # 单个对象：转换为单行
                    df = pd.DataFrame([json_data])
            else:
                raise ValueError("不支持的JSON格式")

            # 保存为Excel
            df.to_excel(output_path, index=False, engine='openpyxl')
            print("✓ JSON转Excel成功")
            return output_path

        except Exception as e:
            print(f"✗ 转换失败: {str(e)}")
            raise Exception(f"JSON转Excel失败: {str(e)}")

    def csv_to_json(self, csv_path, output_dir):
        """
        CSV转JSON
        :param csv_path: CSV文件路径
        :param output_dir: 输出目录
        :return: 输出文件路径
        """
        if not os.path.exists(csv_path):
            raise FileNotFoundError(f"文件不存在: {csv_path}")

        base_name = os.path.splitext(os.path.basename(csv_path))[0]
        output_path = os.path.join(output_dir, f"{base_name}.json")

        print(f"\n开始转换: {os.path.basename(csv_path)}")

        try:
            # 尝试多种编码读取CSV
            encodings = ['utf-8-sig', 'utf-8', 'gbk', 'gb2312', 'latin1']
            df = None
            for encoding in encodings:
                try:
                    df = pd.read_csv(csv_path, encoding=encoding)
                    break
                except:
                    continue

            if df is None:
                raise Exception("无法读取CSV文件，请检查文件编码")

            # 转换为JSON
            json_data = df.to_dict(orient='records')

            # 保存为JSON文件
            with open(output_path, 'w', encoding='utf-8') as f:
                json.dump(json_data, f, ensure_ascii=False, indent=2)

            print("✓ CSV转JSON成功")
            return output_path

        except Exception as e:
            raise Exception(f"CSV转JSON失败: {str(e)}")

    def json_to_csv(self, json_path, output_dir):
        """
        JSON转CSV
        :param json_path: JSON文件路径
        :param output_dir: 输出目录
        :return: 输出文件路径
        """
        if not os.path.exists(json_path):
            raise FileNotFoundError(f"文件不存在: {json_path}")

        base_name = os.path.splitext(os.path.basename(json_path))[0]
        output_path = os.path.join(output_dir, f"{base_name}.csv")

        print(f"\n开始转换: {os.path.basename(json_path)}")

        try:
            # 读取JSON文件
            with open(json_path, 'r', encoding='utf-8') as f:
                json_data = json.load(f)

            # 转换为DataFrame
            if isinstance(json_data, list):
                df = pd.DataFrame(json_data)
            elif isinstance(json_data, dict):
                df = pd.DataFrame([json_data])
            else:
                raise ValueError("不支持的JSON格式")

            # 保存为CSV
            df.to_csv(output_path, index=False, encoding='utf-8-sig')

            print("✓ JSON转CSV成功")
            return output_path

        except Exception as e:
            raise Exception(f"JSON转CSV失败: {str(e)}")

    def ods_to_excel(self, ods_path, output_dir):
        """
        ODS转Excel（OpenDocument Spreadsheet）
        :param ods_path: ODS文件路径
        :param output_dir: 输出目录
        :return: 输出文件路径
        """
        if not os.path.exists(ods_path):
            raise FileNotFoundError(f"文件不存在: {ods_path}")

        if not ods_path.lower().endswith('.ods'):
            raise ValueError(f"不是ODS文件: {ods_path}")

        base_name = os.path.splitext(os.path.basename(ods_path))[0]
        output_path = os.path.join(output_dir, f"{base_name}.xlsx")

        print(f"\n开始转换: {os.path.basename(ods_path)}")
        print("=" * 60)

        try:
            # 使用pandas读取ODS文件（需要odfpy库）
            # 读取所有工作表
            import warnings
            warnings.filterwarnings('ignore')

            # pandas可以直接读取ODS
            ods_file = pd.ExcelFile(ods_path, engine='odf')

            with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
                for sheet_name in ods_file.sheet_names:
                    df = pd.read_excel(ods_file, sheet_name=sheet_name)
                    df.to_excel(writer, sheet_name=sheet_name, index=False)

            print("✓ ODS转Excel成功")
            return output_path

        except ImportError:
            print("✗ 缺少odfpy库")
            raise Exception("ODS转Excel失败：请安装odfpy库 (pip install odfpy)")
        except Exception as e:
            print(f"✗ 转换失败: {str(e)}")
            raise Exception(f"ODS转Excel失败: {str(e)}")

    def excel_to_ods(self, excel_path, output_dir):
        """
        Excel转ODS（OpenDocument Spreadsheet）
        :param excel_path: Excel文件路径
        :param output_dir: 输出目录
        :return: 输出文件路径
        """
        if not os.path.exists(excel_path):
            raise FileNotFoundError(f"文件不存在: {excel_path}")

        ext = excel_path.lower()
        if not (ext.endswith('.xls') or ext.endswith('.xlsx')):
            raise ValueError(f"不是Excel文件: {excel_path}")

        base_name = os.path.splitext(os.path.basename(excel_path))[0]
        output_path = os.path.join(output_dir, f"{base_name}.ods")

        print(f"\n开始转换: {os.path.basename(excel_path)}")
        print("=" * 60)

        try:
            # 读取Excel文件
            xls = pd.ExcelFile(excel_path)

            # 使用odf引擎写入ODS
            with pd.ExcelWriter(output_path, engine='odf') as writer:
                for sheet_name in xls.sheet_names:
                    df = pd.read_excel(xls, sheet_name=sheet_name)
                    df.to_excel(writer, sheet_name=sheet_name, index=False)

            print("✓ Excel转ODS成功")
            return output_path

        except ImportError:
            print("✗ 缺少odfpy库")
            raise Exception("Excel转ODS失败：请安装odfpy库 (pip install odfpy)")
        except Exception as e:
            print(f"✗ 转换失败: {str(e)}")
            raise Exception(f"Excel转ODS失败: {str(e)}")
